<?php
class Common extends controller {
	
	function Common(){
		
		parent::Controller(); 
		$this->load->library("rapyd");
				
	}
	function liqui($cambio){
		
	   $this->db->query("DROP TABLE IF EXISTS liqui");
       //$this->db->query("");
       
       $mSQL="CREATE TABLE IF NOT EXISTS liqui SELECT CONCAT(IF(a.tipo_doc='AN','G',a.tipo_doc),a.seriep,'-',a.factura)comp,
       b.cuenta,c.descrip ncuenta,a.proveed,a.nombre,IF(a.tipo_doc='AN','G',a.tipo_doc)tipo_doc,a.seriep,a.factura,
        IF(a.moneda='02',monto,ROUND(monto/$cambio,2)) montod,
        IF(a.moneda='01',monto,ROUND(monto*$cambio,2)) montos,'$cambio' as cambio,a.fecha,
        a.abonos,a.moneda
        FROM sprm as a 
        LEFT JOIN gser as b on a.transac=b.transac
        LEFT JOIN cpla as c on b.cuenta=c.cuenta
        WHERE a.tipo_doc NOT IN ('AB','D','L') AND a.monto > a.abonos
        UNION
        SELECT CONCAT(IF(a.tipo_doc='AN','G',a.tipo_doc),a.seriei,'-',a.factura)comp,
		b.cuenta,c.descrip ncuenta,a.proveed,a.nombre,IF(a.tipo_doc='AN','G',a.tipo_doc)tipo_doc,a.seriep,a.factura,
        IF(a.moneda='02',monto,ROUND(monto/$cambio,2)) montod,
        IF(a.moneda='01',monto,ROUND(monto*$cambio,2)) montos,'$cambio' as cambio,a.fecha,
        a.abonos,a.moneda
        FROM sprm as a 
        LEFT JOIN gser as b on a.transac=b.transac
        LEFT JOIN cpla as c on b.cuenta=c.cuenta
        WHERE a.tipo_doc='L' and a.seriei='007' AND a.monto > a.abonos";
       $this->db->query($mSQL);
       echo $mSQL;
	
	}
	function moneda($seriei=null){
		
		$moneda=$this->datasis->dameval("SELECT moneda FROM bancos WHERE codigo='$seriei'");
		echo $moneda;
	}
	function cfecha($fecha){
		
		$cambio=$this->datasis->dameval("SELECT cambio FROM cambio WHERE fecha='$fecha'");
		echo $cambio;
	}
	function numcruce($seriei=null,$comp=null){
				
		$ano=date("Y");
        $mes=date("m");
        
        $num=$this->datasis->dameval("SELECT RIGHT((numeroi+1),5)valor FROM cruce WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' ORDER BY numeroi DESC LIMIT 1");
        $num=str_pad($num,5,'0', STR_PAD_LEFT);
                
        if($num=='00000' OR $num==''){
			$num='00001';
		}
		
		echo ($ano.$mes.$num);
		
	}	
	function numgser($seriei=null,$comp=null){
				
		$ano=date("Y");
        $mes=date("m");
        
        $num=$this->datasis->dameval("SELECT RIGHT((numeroi+1),5)valor FROM gser WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND seriei='$seriei' AND comp='$comp' ORDER BY numeroi DESC LIMIT 1");
        $num=str_pad($num,5,'0', STR_PAD_LEFT);
                
        if($num=='00000' OR $num==''){
			$num='00001';
		}
		
		echo ($ano.$mes.$num);
		
	}
	function numscst($seriei=null){
				
		$ano=date("Y");
        $mes=date("m");
        
        $num=$this->datasis->dameval("SELECT RIGHT((numeroi+1),5)valor FROM scst WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND seriei='$seriei' ORDER BY numeroi DESC LIMIT 1");
        $num=str_pad($num,5,'0', STR_PAD_LEFT);
                
        if($num=='00000' OR $num==''){
			$num='00001';
		}
		
		echo ($ano.$mes.$num);
		
	}
    function getconcilia($id,$chek){
                
        $usuario=$this->session->userdata['usuario'];
        $this->db->query("UPDATE comprobantes SET conciliado='$chek',estampac=CURRENT_TIMESTAMP(),usuario='$usuario' WHERE numerador='$id'");
        
    }
    function getconciliat($banco,$mes,$ano,$chek){
        
        IF($chek=='false'){$chek='N';}else{$chek='1';}
        
        $banco=str_pad($banco,3,'0', STR_PAD_LEFT);
        
        $usuario=$this->session->userdata['usuario'];
        
        $this->db->query("UPDATE comprobantes SET conciliado='$chek',estampac=CURRENT_TIMESTAMP(),usuario='$usuario' WHERE ccomp='$banco' AND MONTH(fecha)='$mes' AND YEAR(fecha)='$ano'");
        
    }
	function numegreso($seriei=null){
		
		$ano=date("Y");
        $mes=date("m");
        
        $chek=$this->datasis->dameval("SELECT numero FROM itppro WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND serie='$seriei' ORDER BY numero DESC LIMIT 1");
        
        $chek2=$this->datasis->dameval("SELECT numero FROM bmov WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND banco='$seriei' AND clipro='P' ORDER BY numero DESC LIMIT 1");
        
        if($chek>$chek2){
        
        $num=$this->datasis->dameval("SELECT RIGHT((numero+1),5)valor FROM itppro WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND serie='$seriei' ORDER BY numero DESC LIMIT 1");
        
		}else{
			
		$num=$this->datasis->dameval("SELECT RIGHT((numero+1),5)valor FROM bmov WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND banco='$seriei' AND clipro='P' ORDER BY numero DESC LIMIT 1");
       	
		}
        
        $num=str_pad($num,5,'0', STR_PAD_LEFT);
        
        if($num=='00000' OR $num==''){
			$num='00001';
		}
		
		echo ($ano.$mes.$num);
		
	}
	function numrecibo($seriei=null){
		
		$ano=date("Y");
        $mes=date("m");
        
        $chek=$this->datasis->dameval("SELECT numero FROM itabonos WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND serie='$seriei' ORDER BY numero DESC LIMIT 1");
        
        $chek2=$this->datasis->dameval("SELECT numero FROM bmov WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND banco='$seriei' AND clipro='C' ORDER BY numero DESC LIMIT 1");
        
        if($chek>$chek2){
        
        $num=$this->datasis->dameval("SELECT RIGHT((numero+1),5)valor FROM itabonos WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND serie='$seriei' ORDER BY numero DESC LIMIT 1");
        
		}else{
			
		$num=$this->datasis->dameval("SELECT RIGHT((numero+1),5)valor FROM bmov WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND banco='$seriei' AND clipro='C' ORDER BY numero DESC LIMIT 1");
       	
		}
        
        //$num=$this->datasis->dameval("SELECT RIGHT((numero+1),5)valor FROM itabonos WHERE YEAR(fecha)='$ano' AND MONTH(fecha)='$mes' AND serie='$seriei' ORDER BY numero DESC LIMIT 1");
        //$num=str_pad($num,5,'0', STR_PAD_LEFT);
        
        if($num=='00000' OR $num==''){
			$num='00001';
		}
		
		echo ($ano.$mes.$num);
		
	}
	function getcomp($comp=null){//usado por GSER
		echo "<option value=''>Seleccione un comprobante</option>";
		//$comp=$this->input->post('comp');
		if(!empty($comp)){
			
			IF($comp=='P')
				$mSQL=$this->db->query("SELECT codigo, CONCAT_WS('-',codigo,descrip) AS descrip FROM seriep ORDER BY codigo");
			else{
				IF($comp=='ND'){
					$mSQL=$this->db->query("SELECT codigo, CONCAT_WS('-',codigo,descrip) AS descrip FROM seriep  WHERE codigo='010' ORDER BY codigo");
				}else{
					$mSQL=$this->db->query("SELECT codigo, CONCAT_WS('-',codigo,descrip) AS descrip FROM seriel  WHERE codigo<>'006' ORDER BY codigo");
				}
			}
			
			if($mSQL){
				foreach($mSQL->result() AS $fila ){
					echo "<option value='".$fila->codigo."'>".$fila->descrip."</option>";
				}
			}
		}
	}
}
